'''
Created on 2012-3-27
操作oracle数据库,真蛋疼
还得安装xc_Oracle才能用
@author: computer


connection,cursor需要关闭的，万年不变的定律!
'''

import cx_Oracle

#获得链接
def getConn():
        
    #connection = cx_Oracle.Connection("scott/tiger@161_orcl")
    username = 'scott'
    password = 'tiger'
    host = '192.168.20.161'
    port = 1521
    dbname = 'orcl'
    dsn = cx_Oracle.makedsn(host,port,dbname)
    connection = cx_Oracle.connect(username,password,dsn)
    
    return connection


def findList(sql):
    #建立链接
    connection = getConn()

#try:
#    cursor.execute("select 1 / 0 from dual")
#except cx_Oracle.DatabaseError, exc:
#    error, = exc.args
#    print >> sys.stderr, "Oracle-Error-Code:", error.code
#    print >> sys.stderr, "Oracle-Error-Message:", error.message
    
    #创建游标
    cursor = connection.cursor()
    #执行语句
    cursor.execute(sql)
    #不起作用，郁闷
    count = cursor.rowcount
    print('查询的行数: '+str(count))
    #查询结构
    result = cursor.fetchall()
    print('结果集行数：'+str(len(result)))
    #遍历结果集
    for row in result:
        print(row)
        
    #关闭还是需要的
    cursor.close()
    connection.close()
    print(connection)
    
def save(sql,param):

    conn = getConn()
    
    cursor = conn.cursor()
    
    #绑定变量的方式
#    cursor.execute('insert into A values(:id,:name,:salary)',{'id':13,'name':'andy','salary':'333344'})
    
    print('execute：'+str(cursor.execute(sql,param)))
    print('rowcount数量：'+str(cursor.rowcount))
    #必须要提交
    conn.commit()
    
    cursor.close()
    
    conn.close()   
    
def remove(id):
    
    conn = getConn()
    cursor = conn.cursor()
    
    cursor.execute('delete from A where id = :id',{'id':id})
    print('count:'+str(cursor.rowcount))
    conn.commit()
    
    cursor.close()
    conn.close(); 
# TEST************************************    
    
#findList("select * from emp")

#save("insert into A values('2','2','3')")

#save('insert into A values(:id,:name,:salary)',{'id':13,'name':'andy','salary':'333344'})

#remove(13)

